﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace Dashboard.ProductBonus.Business
{
    public class AttendanceReport : BusinessBase
    {
        public DataSet GetAttendanceSummaryReport(DateTime fromDate, DateTime toDate, string team, string shift, string staffNo)
        {
            string sql = @"
            SELECT   a.StaffNo, a.Name, a.Dept, a.Team, a.Shift,
                     isnull ((SELECT SUM (b.totaltime)
                                FROM abnormalworkingtime b
                               WHERE b.reasoncode in ('14','15')
                                 AND b.staffno = a.staffno
                                 AND b.DATE >= '{0}'
                                 AND b.DATE <= '{1}'),
                             0
                            ) AS Overtime,
                     isnull ((SELECT SUM (b.totaltime)
                                FROM abnormalworkingtime b INNER JOIN nonworkingreason c ON b.reasoncode = c.pcode
                               WHERE c.pvalue3 = 'Y'
                                 AND b.staffno = a.staffno
                                 AND b.DATE >= '{0}'
                                 AND b.DATE <= '{1}'),
                             0
                            ) AS AbsentTime
                FROM namelist a
               WHERE 1 = 1 {2} AND a.staffno LIKE '{3}%'
            ORDER BY a.dept, a.team, a.shift, a.staffno
            ";

            string condition = "";
            if (!string.IsNullOrEmpty(team))
            {
                condition += " AND a.team = '" + team + "' ";
            }
            if (!string.IsNullOrEmpty(shift))
            {
                condition += " AND a.shift = '" + shift + "' ";
            }

            sql = string.Format(sql, fromDate.ToString("yyyy-MM-dd"), toDate.ToString("yyyy-MM-dd"), condition, staffNo);

            DataTable headInfo = DbConnection.DataSet(sql).Tables[0].Copy();
            headInfo.TableName = "Header";

            sql = @"
            SELECT   c.StaffNo, c.Date, c.ReasonCode AS Reason, c.TotalTime, c.FromTime, c.ToTime
                FROM abnormalworkingtime c 
                INNER JOIN namelist a on c.staffno = a.staffno
                INNER JOIN nonworkingreason b ON c.reasoncode = b.pcode AND b.pvalue3 = 'Y'
                WHERE c.DATE >= '{0}' AND c.DATE <= '{1}' {2} AND a.staffno like '{3}%' 
            ORDER BY c.DATE DESC, CONVERT (int, c.reasoncode) ASC
            ";
            sql = string.Format(sql, fromDate.ToString("yyyy-MM-dd"), toDate.ToString("yyyy-MM-dd"), condition, staffNo);
            DataTable detailInfo = DbConnection.DataSet(sql).Tables[0].Copy();
            detailInfo.TableName = "Detail";

            DataSet result = new DataSet();
            result.Tables.Add(headInfo);
            result.Tables.Add(detailInfo);

            result.Relations.Add(new DataRelation("Detail", result.Tables[0].Columns["StaffNo"], result.Tables[1].Columns["StaffNo"]));

            result.AcceptChanges();
            return result;
        }

        public DataTable GetAttendanceDiffReport(DateTime fromDate, DateTime toDate, string team, string shift, string staffNo, bool onlyShowDiff)
        {
            string sql = @"
            select a.StaffNo,a.Name,a.Dept,a.Team,a.Shift,b.WorkingDate as Date,
	               c.Code AS WorkingShift, b.WorkingShift AS WorkingShiftID, c.FromTime,c.ToTime,d.OndutyTime AS EntryTime,d.offdutytime as LeaveTime,
                   Onduty = CASE WHEN isnull(d.OndutyTime,0)<>0 and isnull(d.offdutytime,0)<>0 THEN 'Y' ELSE 'N' END
            from namelist a 
            inner join WorkerShiftSchedule b on a.staffno=b.staffno
            left join WorkingShift c on b.WorkingShift = c.id
            left join StaffAttendance d on a.staffno=d.staffno and b.WorkingDate = d.AttendanceDate
            where 1=1 AND a.StaffNo LIKE '{0}%' {1}
            AND b.WorkingDate >= '{2}'
            AND b.WorkingDate <= '{3}'
            ORDER BY a.Dept,a.Team,a.Shift,a.StaffNo,b.WorkingDate
            ";

            string condition = "";
            if (!string.IsNullOrEmpty(team))
            {
                condition += " AND a.team = '" + team + "' ";
            }
            if (!string.IsNullOrEmpty(shift))
            {
                condition += " AND a.shift = '" + shift + "' ";
            }

            sql = string.Format(sql, staffNo, condition, fromDate.ToString("yyyy-MM-dd"), toDate.ToString("yyyy-MM-dd"));

            DataTable result = DbConnection.DataSet(sql).Tables[0];

            if (onlyShowDiff)
            {
                DataRow row;
                for (int i = result.Rows.Count - 1; i >= 0; i--)
                {
                    row = result.Rows[i];

                    if (row["WorkingShiftID"].ToString() == "-1" && row["Onduty"].ToString() == "N")
                    {
                        result.Rows.RemoveAt(i);
                        continue;
                    }

                    if (row["WorkingShiftID"].ToString() != "-1" && row["Onduty"].ToString() == "Y")
                    {
                        result.Rows.RemoveAt(i);
                        continue;
                    }
                }
                result.AcceptChanges();
            }

            return result;
            
        }

        public DataTable GetAttendanceReportStaffList(DateTime fromDate, DateTime toDate, string team, string shift, string staffNo)
        {
            string sql = @"
            select a.StaffNo,a.Name,a.Dept,a.Team,a.Shift,b.WorkingDate as Date,
	               b.WorkingShift,
	               Onduty = CASE WHEN isnull(d.OndutyTime,0)<>0 and isnull(d.offdutytime,0)<>0 THEN 'Y' ELSE 'N' END
            from namelist a 
            inner join WorkerShiftSchedule b on a.staffno=b.staffno
            left join WorkingShift c on b.WorkingShift = c.id
            left join StaffAttendance d on a.staffno=d.staffno and b.WorkingDate = d.AttendanceDate
            where 1=1 AND a.StaffNo LIKE '{0}%' {1}
            AND b.WorkingDate >= '{2}'
            AND b.WorkingDate <= '{3}'
            ORDER BY a.Dept,a.Team,a.Shift,a.StaffNo,b.WorkingDate 
            ";

            string condition = "";
            if (!string.IsNullOrEmpty(team))
            {
                condition += " AND a.team = '" + team + "' ";
            }
            if (!string.IsNullOrEmpty(shift))
            {
                condition += " AND a.shift = '" + shift + "' ";
            }

            sql = string.Format(sql, staffNo, condition, fromDate.ToString("yyyy-MM-dd"), toDate.ToString("yyyy-MM-dd"));

            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetAllAbsentRecord(DateTime fromDate, DateTime toDate, string team, string shift, string staffNo)
        {
            string sql = @"
            SELECT b.StaffNo,b.ReasonCode,b.Date,b.TotalTime 
              FROM AbnormalWorkingTime b INNER JOIN namelist a ON b.StaffNo = a.StaffNo
                   INNER JOIN NonworkingReason c on b.ReasonCode = c.pCode AND c.pvalue3='Y'
             WHERE 1=1 AND b.StaffNo like '{0}%' {1} 
               AND b.Date >= '{2}'
               AND b.Date <= '{3}'
            ";

            string condition = "";
            if (!string.IsNullOrEmpty(team))
            {
                condition += " AND a.team = '" + team + "' ";
            }
            if (!string.IsNullOrEmpty(shift))
            {
                condition += " AND a.shift = '" + shift + "' ";
            }

            sql = string.Format(sql, staffNo, condition, fromDate.ToString("yyyy-MM-dd"), toDate.ToString("yyyy-MM-dd"));

            return DbConnection.DataSet(sql).Tables[0];
        }

        public DataTable GetStaffTimeCardList(DateTime fromDate, DateTime toDate, string staffNo)
        {
            string sql = @"
            SELECT a.StaffNo,a.Name,a.Dept,a.Team,a.Shift,a.CardNo,b.DeviceNo,b.TakenDateTime 
              FROM TimeCardInfo b Inner Join NameList a ON b.CardNo = a.CardNo
             WHERE a.StaffNo = '{0}'
               AND Convert(VARCHAR,b.TakenDateTime,112) >= '{1}'
               AND Convert(VARCHAR,b.TakenDateTime,112) <= '{2}'
             ORDER BY a.StaffNo,b.TakenDateTime
            ";

            sql = string.Format(sql, staffNo, fromDate.ToString("yyyyMMdd"), toDate.ToString("yyyyMMdd"));

            return DbConnection.DataSet(sql).Tables[0];
        }
    }
}
